
var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var YjxBaseModel = require('./YjxBaseModel');
var CONFIG = require('../../config');


var YjxUniUserDocModel = function (obj) {
    YjxBaseModel.call(this,obj);
};

YjxUniUserDocModel.prototype = new YjxBaseModel();

// 取得表名
YjxUniUserDocModel.prototype.getTableName = function(){
    return 'yjx_uni_user_doc';
};
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
YjxUniUserDocModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'uni_doc_id='+this.uni_doc_id;
};


/**
 * 查询用户列表
 *
 * @deprecated 老的获取用户作品列表（废弃）
 * @author 严旭东
 * @date 2017-11-17
 * @param bForCnt 判断查询列表个数或列表
 * @param userId 用户唯一标识
 * @param pageNum 页码, 从0开始
 * @param pageSize 页容量, 大于0
 * @param opTypes 用户作品类型区分
 * @return 查询列表个数或列表的sql
 */
function sql_getUserWorkList(bForCnt,userId,pageNum,pageSize,opTypes){
    var sql = '';
    if(bForCnt){
        sql += ' SELECT COUNT(*) as count '
    }else{
        sql+="	SELECT	" +
            "	  res.*,	" +
            "	  yst.title AS typeDesc,	" +
            "	  DATE_FORMAT(res.timeOrder, '%Y-%m-%d') AS `time`	" ;
    }
    sql+="	FROM	" +
        "	  (SELECT	" +
        "	    vud.uni_doc_id AS id,	" +
        "	    '2' AS opType,	" +
        "	    vud.doc_name AS `name`,	" +
        "	    vdd.theme_thumb AS thumb,	" +
        "	    ts.width AS w,	" +
        "	    ts.height AS h,	" +
        "	    vud.create_time AS `timeOrder`,	" +
        "	    vud.state,	" +
        "	    uud.watermark,	" +
        "	    uud.wm_paid AS watermarkPay,	" +
        "	    uud.show_type_id AS st_id	" +
        "	  FROM	" +
        "	    yjx.yjx_uni_user_doc uud,	" +
        "	    yjx.yjx_video_user_doc vud,	" +
        "	    itbour.video_design_doc vdd	" +
        "	    LEFT JOIN itbour.tg_size ts	" +
        "	      ON vdd.size_id = ts.size_id	" +
        "	      AND ts.del_flg = 0	" +
        "	  WHERE uud.user_id = "+userId+"	" +
        "	    AND uud.uni_doc_id = vud.uni_doc_id	" +
        "	    AND vud.design_id = vdd.design_id	" +
        "	    AND uud.del_flg = 0	" +
        "	    AND vud.del_flg = 0	" +
        "	    AND vdd.del_flg = 0	" +
        "	  UNION	" +
        "	  ALL	" +
        "	  SELECT	" +
        "	    dud.uni_doc_id AS id,	" +
        "	    '1' AS opType,	" +
        "	    dud.doc_name AS `name`,	" +
        "	    IFNULL(dup.thumb, tdp.thumb) AS thumb,	" +
        "	    ts.width AS w,	" +
        "	    ts.height AS h,	" +
        "	    dud.create_time AS `timeOrder`,	" +
        "	    '0' AS state,	" +
        "	    uud.watermark,	" +
        "	    uud.wm_paid AS watermarkPay,	" +
        "	    uud.show_type_id AS st_id	" +
        "	  FROM	" +
        "	    itbour.tg_type tt,	" +
        "	    itbour.tg_size ts,	" +
        "	    yjx.yjx_uni_user_doc uud,	" +
        "	    yjx.yjx_design_user_doc dud	" +
        "	    LEFT JOIN yjx.yjx_design_user_page dup	" +
        "	      ON dud.`doc_id` = dup.doc_id	" +
        "	      AND dup.del_flg = 0	" +
        "	      AND dup.page_index = 0	" +
        "	    LEFT JOIN itbour.tg_design_page tdp	" +
        "	      ON tdp.design_id = dud.`design_id`	" +
        "	      AND tdp.del_flg = 0	" +
        "	      AND tdp.page_index = 0	" +
        "	  WHERE uud.user_id = "+userId+"	" +
        "	    AND dud.uni_doc_id = uud.uni_doc_id	" +
        "	    AND dud.size_id = ts.size_id	" +
        "	    AND dud.type_id = tt.type_id	" +
        "	    AND tt.top_parent <> 1030	" +
        "	    AND dud.del_flg = 0	" +
        "	    AND uud.`del_flg` = 0	" +
        "	    AND tt.del_flg = 0	" +
        "	    AND ts.del_flg = 0	" +
        "	  UNION	" +
        "	  ALL	" +
        "	  SELECT	" +
        "	    uud.uni_doc_id AS id,	" +
        "	    '4' AS opType,	" +
        "	    cvud.doc_name AS `name`,	" +
        "	    CASE WHEN cvud.state = 3 THEN	" +
        "	    itbour.func_parse_string_array (cvud.thumbs, 0)	" +
        "	    ELSE	" +
        "	    'http://itbour-back.oss-cn-hangzhou.aliyuncs.com/image/U2/2018/07/05/171758351_GXBi4Eixs4iIdbVGsAYc'	" +
        "	    END	" +
        "	     AS thumb,	" +
        "	    ts.width AS w,	" +
        "	    ts.height AS h,	" +
        "	    cvud.create_time AS `timeOrder`,	" +
        "	    cvud.state,	" +
        "	    uud.watermark,	" +
        "	    uud.wm_paid AS watermarkPay,	" +
        "	    uud.show_type_id AS st_id	" +
        "	  FROM	" +
        "	    yjx.yjx_uni_user_doc uud,	" +
        "	    yjx.yjx_cust_video_user_doc cvud	" +
        "	    LEFT JOIN itbour.tg_size ts	" +
        "	      ON cvud.size_id = ts.size_id	" +
        "	      AND ts.del_flg = 0	" +
        "	  WHERE uud.user_id = "+userId+"	" +
        "	    AND uud.uni_doc_id = cvud.uni_doc_id	" +
        "	    AND uud.user_id = cvud.user_id	" +
        "	    AND cvud.purpose = 0	" +
        "	    AND cvud.del_flg = 0	" +
        "	    AND uud.del_flg = 0) res	" +
        "	  LEFT JOIN yjx.yjx_show_type yst	" +
        "	    ON yst.del_flg = 0	" +
        "	    AND res.st_id = yst.st_id	" ;
        if(opTypes){
          sql += "	  WHERE res.opType IN ("+opTypes+")	" ;
        }

        sql +="	ORDER BY res.timeOrder DESC,	";
        sql += "	  res.id DESC	" ;
    if(!bForCnt){
        if (typeof pageNum !== 'undefined' && pageNum !== null &&
            typeof pageSize !== 'undefined' && pageSize !== null)
        {
            var skipCnt = Number(pageNum||'0')*Number(pageSize||'0');
            sql += " LIMIT " + skipCnt + "," + Number(pageSize||'0');
        }
    }
    return sql;
}


/**
 * 确定表名
 *
 * @author 严旭东
 * @date 2017-11-17
 *
 * @param userId 用户唯一标识
 * @param docId 作品唯一标识
 * @param callback  回调
 *          err: 查询失败
 *          results : [10,20,30]
 *          注：10代表yjx_design_user_doc
 *              20代表yjx_ppt_user_doc
 *              30代表yjx_video_user_doc
 */
YjxUniUserDocModel.prototype.definiteTableName = function(userId,docId,callback){
    pool.pool.getConnection(function(getConnectErr,connection) {
        if (getConnectErr) {
            console.log('YjxUniUserDocModel.removeUserDoc_v1_0 getConnectErr: ' + getConnectErr);
            return callback(getConnectErr, null);
        }
        var sql =   '  SELECT 10 AS tmpl_type ' +
                    '  FROM yjx.yjx_design_user_doc ydud, ' +
                    '       itbour.tg_type tt ' +
                    '  WHERE ydud.uni_doc_id = ' + docId +
                    '  	    AND ydud.del_flg = 0 ' +
                    '  	    AND ydud.user_id = ' + userId +
                    '  	    AND tt.del_flg = 0 ' +
                    '  	    AND tt.type_id = ydud.type_id ' +
                    '  	    AND tt.top_parent != 1030 ' +       // modify by 王俊, 2017-11-18 增加视频背景判断条件
                    '  UNION ALL ' +
                    '  SELECT 20 AS tmpl_type ' +
                    '  FROM yjx.yjx_ppt_user_doc ypud ' +
                    '  WHERE ypud.uni_doc_id = ' + docId +
                    '  	    AND ypud.del_flg = 0 ' +
                    '  	    AND ypud.user_id = ' +userId +
                    '  UNION ALL ' +
                    '  SELECT 30 AS tmpl_type ' +
                    '  FROM yjx.yjx_video_user_doc yvud ' +
                    '  WHERE yvud.uni_doc_id = ' + docId +
                    '  	    AND yvud.del_flg = 0 ' +
                    '  	    AND yvud.user_id = ' + userId+
                    '   UNION  ALL' +
                    '	SELECT	' +              //modify 毛凯 18.7.4  增加40类型为自由创建作品
                    '       40 AS tmpl_type' +
                    '   FROM   '		+
                    '	  yjx.yjx_cust_video_user_doc cvud	' +
                    '	WHERE cvud.uni_doc_id = '+ docId +
                    '	  AND cvud.del_flg = 0	' +
                    '	  AND cvud.user_id = ' + userId;
            console.log(sql);
        connection.query(sql, function (error, results) {
                connection.release();
                if (error) {
                    console.log('YjxUserModel.removeUserDoc_v1_0 Err: ' + error);
                    return callback(error, null);
                }
                return callback(null, results)
            })
    })
};

/**
 * 检查统一用户作品类型
 *
 * @author 王俊
 * @date 2017-11-18
 *
 * @param uniDocId 统一用户作品 id
 * @param callback
 *          err 检查出错,
 *          docInfo: {
 *              uni_doc_id : 用户统一作品 id,
 *              doc_type: 作品类型, 与模板一致, 10 标记设计作品, 20 标记 ppt 作品, 30 标记视频作品
 *              doc_id: 作品子表唯一标识
 *              doc_design_id: 作品对应的模板 id(子表)
 *              video_bg_id: 如果是视频分类且有视频背景, 则为背景 id, 没有视频背景则为 null
 *              video_bg_type: 如果是视频分类且有视频背景, 则为背景分类, 与 doc_type 分类一致: 10 标记设计作品, 20 标记 ppt 作品, 30 标记视频作品 没有视频背景则为 null
 *              bg_design_id: 背景对应的背景模板 id(子表)
 *              form_flg: '0:不使用表单, 1:使用表单(开启), 2:使用表单(关闭)',
 *              watermark: 作品是否有水印
 *              wm_paid: 是否已支付去水印费用
 *              modify_cnt:用户作品修改次数
 *              user_id: 作品对应用户标识
 *          }
 */
YjxUniUserDocModel.prototype.checkUniUserDocType = function (uniDocId, callback) {
    pool.pool.getConnection(function (getConErr, connection) {
        if(getConErr) {
            console.log('YjxUniUserDocMode [checkUniDocType] getConErr: ' + getConErr);
            return callback(getConErr, null);
        }
        var sql =
            ' SELECT ydud.uni_doc_id, ' +
            '        10 AS doc_type, ' +
            '        ydud.doc_id, ' +
            '        if(tt.top_parent=1030, 1, 0) as video_bg, ' +
            '        yuud.watermark, ' +
            '        yuud.wm_paid, ' +
            '        yuud.form_flg, ' +
            '        ydud.design_id AS design_id,' +
            '        DATE_FORMAT(yuud.create_time,"%Y-%m-%d") AS createDay, ' +
            '        0 AS  modify_cnt,  '+
            '        yuud.user_id ' +
            ' FROM yjx.yjx_uni_user_doc yuud, ' +
            '      yjx.yjx_design_user_doc ydud, ' +
            '      itbour.tg_type tt ' +
            ' WHERE 1 ' +
            '   AND ydud.uni_doc_id = ' + uniDocId +
            '   AND tt.type_id = ydud.type_id ' +
            '   AND yuud.uni_doc_id = ydud.uni_doc_id ' +
            '   AND yuud.del_flg=0 ' +
            '   AND ydud.del_flg=0 ' +
            '   AND tt.del_flg=0 ' +
            ' UNION ALL ' +
            ' SELECT ypud.uni_doc_id, ' +
            '        20 AS doc_type, ' +
            '        ypud.doc_id, ' +
            '        0 as video_bg, ' +
            '        yuud.watermark, ' +
            '        yuud.wm_paid, ' +
            '        yuud.form_flg, ' +
            '        ypud.base_doc_id  AS design_id,' +
            '        DATE_FORMAT(yuud.create_time,"%Y-%m-%d") AS createDay, ' +
            '        0 AS  modify_cnt,  '+
            '        yuud.user_id ' +
            ' FROM yjx.yjx_ppt_user_doc ypud, ' +
            '      yjx.yjx_uni_user_doc yuud ' +
            ' WHERE ypud.uni_doc_id = ' + uniDocId +
            '   AND yuud.uni_doc_id = ypud.uni_doc_id ' +
            '   AND ypud.del_flg=0 ' +
            '   AND yuud.del_flg=0 ' +
            ' UNION ALL ' +
            ' SELECT yvud.uni_doc_id, ' +
            '        30 AS doc_type, ' +
            '        yvud.doc_id, ' +
            '        0 as video_bg, ' +
            '        yuud.watermark, ' +
            '        yuud.wm_paid, ' +
            '        yuud.form_flg, ' +
            '        yvud.design_id  AS design_id,' +
            '        DATE_FORMAT(yuud.create_time,"%Y-%m-%d") AS createDay, ' +
            '        yvud.modify_cnt AS modify_cnt,   '+
            '        yuud.user_id ' +
            ' FROM yjx.yjx_video_user_doc yvud, ' +
            '      yjx.yjx_uni_user_doc yuud ' +
            ' WHERE yvud.uni_doc_id = ' + uniDocId +
            '   AND yuud.uni_doc_id = yvud.uni_doc_id ' +
            '   AND yvud.del_flg=0 ' +
            '   AND yuud.del_flg=0 '+
            // 增加自由创建的check
            'UNION ALL  	' +
            ' SELECT yuud.uni_doc_id,  	' +
            '		40 AS doc_type,  	' +
            '		cvud.doc_id,  	' +
            '		0 AS video_bg,  ' +
            '		yuud.watermark,  '	+
            '		yuud.wm_paid,  	' +
            '		yuud.form_flg,  ' +
            '		cvud.design_id  AS design_id, 	' +
            '		DATE_FORMAT(yuud.create_time,"%Y-%m-%d") AS createDay, ' +
            '		cvud.modify_cnt AS modify_cnt,   	' +
            '		cvud.user_id  	' +
            ' FROM   yjx.yjx_uni_user_doc yuud,	' +
            ' yjx.yjx_cust_video_user_doc cvud	' +
            ' WHERE cvud.uni_doc_id =  '+ uniDocId +
            '   AND cvud.uni_doc_id = yuud.uni_doc_id  ' +
            '   AND cvud.del_flg=0  	' +
            '   AND yuud.del_flg=0 	' ;

        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();

            if(queryErr) {
                console.log('YjxUniUserDocMode [checkUniDocType] queryErr: ' + queryErr);
                return callback(queryErr, null);
            }
            // 如果没有查询到结果, 则作品状态异常
            if(!results || results.length <= 0) {
                return callback('该作品不存在或已删除', null);
            }
            var ret = {};
            for (var i=0; i<results.length; i++) {
                var one = results[i];
                if(!ret[one.uni_doc_id]) {
                    ret[one.uni_doc_id] = {};
                }
                // 是主作品
                if(Number(one.video_bg) === 0) {
                    ret[one.uni_doc_id].uni_doc_id = one.uni_doc_id;
                    ret[one.uni_doc_id].doc_type = one.doc_type;
                    ret[one.uni_doc_id].doc_id = one.doc_id;
                    ret[one.uni_doc_id].watermark = one.watermark;
                    ret[one.uni_doc_id].wm_paid = one.wm_paid;
                    ret[one.uni_doc_id].form_flg = one.form_flg;
                    ret[one.uni_doc_id].doc_design_id = one.design_id;
                    ret[one.uni_doc_id].modify_cnt  = one.modify_cnt;
                    ret[one.uni_doc_id].user_id  = one.user_id;
                }
                // 是视频背景
                if(Number(one.video_bg) === 1) {
                    ret[one.uni_doc_id].video_bg_id = one.doc_id;
                    ret[one.uni_doc_id].video_bg_type = one.doc_type;
                    ret[one.uni_doc_id].bg_design_id = one.design_id;
                }
            }
            var docInfo = ret[uniDocId];
            if(!docInfo) {
                return callback('该作品不存在或已删除');
            }
            docInfo.createDay = results[0].createDay;
            // 没有背景时, 直接取docInfo.video_bg_id 为 undefined 使用 Numer 函数转换结果为 NaN, 此处转为 null, 使用 Numer 函数转换为 0
            if(!docInfo.video_bg_id) {
                docInfo.video_bg_id = null;
                docInfo.video_bg_type = null;
                docInfo.bg_design_id = null;
            }
            return callback(queryErr, docInfo);
        });
    })
};

/**
 * 查询作品列表
 *
 * @author 严旭东
 * @date 2017-11-18
 *
 * @deprecated 老的获取用户作品列表（废弃）
 * @param userId 用户唯一标识
 * @param pageNum 页码, 从0开始
 * @param pageSize 页容量, 大于0
 * @param withTotalPage 0/1, 1 标识获取总页数, 0或不传递标识不获取总页数, 默认为0
 * @param opTypes 获取用户作品类型
 * @param callback  回调
 *          err: 查询失败
 *          data : []
 *
 */
YjxUniUserDocModel.prototype.getUserWorkList_v1_0 = function(userId,pageNum,pageSize,withTotalPage,opTypes,callback){
    pool.pool.getConnection(function(getConnectErr,connection) {
        if (getConnectErr) {
            console.log('YjxUniUserDocModel.getUserWorkList_v1_0 getConnectErr: ' + getConnectErr);
            return callback(getConnectErr, null);
        }
        var sql = sql_getUserWorkList(false,userId,pageNum,pageSize,opTypes);
        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            if (error) {
                console.log('YjxUserModel.getUserWorkList_v1_0 Err: ' + error);
                return callback(error, null);
            }
            return callback(null, results)
        })
    })
};


/**
 * 获取作品列表总个数
 *
 * @author 严旭东
 * @date 2017-11-18
 *
 * @deprecated 老的获取用户作品列表（废弃）
 * @param userId 用户唯一标识
 * @param pageNum 页码, 从0开始
 * @param pageSize 页容量, 大于0
 * @param withTotalPage 0/1, 1 标识获取总页数, 0或不传递标识不获取总页数, 默认为0
 * @param opTypes 获取用户作品类型
 * @param callback  回调
 *          err: 查询失败
 *          data : 数量
 */
YjxUniUserDocModel.prototype.getUserWorkListCnt = function(userId,pageNum,pageSize,withTotalPage,opTypes,callback){
    pool.pool.getConnection(function(getConnectErr,connection) {
        if (getConnectErr) {
            console.log('YjxUniUserDocModel.getUserWorkList_v1_0 getConnectErr: ' + getConnectErr);
            return callback(getConnectErr, null);
        }
        var sql = sql_getUserWorkList(true,userId,pageNum,pageSize,opTypes);
        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            if (error) {
                console.log('YjxUserModel.getUserWorkList_v1_0 Err: ' + error);
                return callback(error, null);
            }
            if (results && results[0]) {
                return callback(null, results[0].count);
            }
        })
    })
};

/**
 * 获取用户视频作品对应的统一项目 id
 *
 * @author 王俊
 * @date 2016-12-12
 *
 * @param uniDocId  统一作品 id
 * @param callback  回调
 *          err: 错误
 *          data: {
 *              uni_item_id: 统一 item id
 *          }
 * 注意: 若查询不到则返回 data 为 null
 */
YjxUniUserDocModel.prototype.getVideoUserDocUniItemId = function (uniDocId, callback) {
    pool.pool.getConnection(function(getConnectErr,connection) {
        if (getConnectErr) {
            console.log('YjxUniUserDocModel.getVideoUserDocUniItemId getConnectErr: ' + getConnectErr);
            return callback(getConnectErr, null);
        }
        var sql = ' SELECT yui.uni_item_id ' +
            ' FROM yjx.`yjx_video_user_doc` yvud, ' +
            '      yjx.`yjx_uni_items` yui ' +
            ' WHERE yvud.uni_doc_id = ' + uniDocId +
            '   AND yui.`item_id` = yvud.design_id ' +
            '   AND yui.item_type = 30 ' +
            '   AND yvud.del_flg=0 ' +
            '   AND yui.del_flg=0 ';
        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            if (error) {
                console.log('YjxUserModel.getVideoUserDocUniItemId Err: ' + error);
                return callback(error, null);
            }
            if(results && results.length >0) {
                return callback(error, results[0]);
            }
            return callback(error, null);
        })
    });
};

/**
 * 获取用户设计作品对应的统一项目 id
 *
 * @author 王俊
 * @date 2016-12-12
 *
 * @param uniDocId  统一作品 id
 * @param callback  回调
 *          err: 错误
 *          data: {
 *              uni_item_id: 统一 item id
 *          }
 * 注意: 若查询不到则返回 data 为 null
 */
YjxUniUserDocModel.prototype. getDesignUserDocUniItemId = function (uniDocId, callback) {
    pool.pool.getConnection(function(getConnectErr,connection) {
        if (getConnectErr) {
            console.log('YjxUniUserDocModel.getDesignUserDocUniItemId getConnectErr: ' + getConnectErr);
            return callback(getConnectErr, null);
        }
        var sql = ' SELECT yui.uni_item_id ' +
            ' FROM yjx.`yjx_design_user_doc` ydud, ' +
            '      yjx.`yjx_uni_items` yui ' +
            ' WHERE ydud.uni_doc_id = ' + uniDocId +
            '   AND yui.`item_id` = ydud.design_id ' +
            '   AND yui.item_type = 10 ' +
            '   AND ydud.del_flg=0 ' +
            '   AND yui.del_flg=0 ';
        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            if (error) {
                console.log('YjxUserModel.getDesignUserDocUniItemId Err: ' + error);
                return callback(error, null);
            }
            if(results && results.length >0) {
                return callback(error, results[0]);
            }
            return callback(error, null);
        })
    });
};

/**
 * 查询用户列表WEB
 *
 * @author 毛凯
 * @date 2017-1-26
 * @param bForCnt 判断查询列表个数或列表
 * @param userId 用户唯一标识
 * @param pageNum 页码, 从0开始
 * @param pageSize 页容量, 大于0
 * @param opType [1]类型
 * @return 查询列表个数或列表的sql
 */
function sql_getUserWorkListWeb(bForCnt,userId,pageNum,pageSize,opType){
    var sql = '';
    if(bForCnt){
        sql += ' SELECT COUNT(0) AS `count` ';
    }else{
        sql+="	SELECT	" +
            "	  res2.*,	" +
            "	  res2.typeDesc,	" +
            "	  res2.`time`	" ;
    }
    sql+=   "	FROM	" +
            "	  (SELECT	" +
            "	    res.*,	" +
            "	    yst.title AS typeDesc,	" +
            "	    DATE_FORMAT(res.timeOrder, '%Y-%m-%d') AS `time`	" +
            "	  FROM	" +
            "	    (SELECT	" +
            "	      vud.uni_doc_id AS id,	" +
            "	      '2' AS opType,	" +
            "	      vud.doc_name AS `name`,	" +
            "	      CASE WHEN vud.state = 3 or vud.state = 1 THEN	" +
            "	      vdd.theme_thumb	" +
            "	      ELSE	" +
            "	      'https://itbour-back.oss-cn-hangzhou.aliyuncs.com/image/U2/2018/07/23/172402714_jRKdZf3QZ54NhEwGV1eW'	" +
            "	      END	" +
            "	      AS thumb,	" +
            "	      ts.width AS w,	" +
            "	      ts.height AS h,	" +
            "	      vud.create_time AS `timeOrder`,	" +
            "         vud.state,                    " +
            "	      uud.watermark,	" +
            "	      uud.wm_paid AS watermarkPay,	" +
            "	      uud.show_type_id AS st_id,	" +
            "	      vdd.duration AS duration	" +
            "	    FROM	" +
            "	      yjx.yjx_uni_user_doc uud,	" +
            "	      yjx.yjx_video_user_doc vud,	" +
            "	      itbour.video_design_doc vdd	" +
            "	      LEFT JOIN itbour.tg_size ts	" +
            "	        ON vdd.size_id = ts.size_id	" +
            "	        AND ts.del_flg = 0	" +
            "	    WHERE uud.user_id = " + userId + "	" +
            "	      AND uud.uni_doc_id = vud.uni_doc_id	" +
            "	      AND vud.design_id = vdd.design_id	" +
            "	      AND uud.del_flg = 0	" +
            "	      AND vud.del_flg = 0	" +
            "	      AND vdd.del_flg = 0	" +
            "	    UNION ALL	" +
            "	    SELECT	" +
            "	      dud.uni_doc_id AS id,	" +
            "	      '1' AS opType,	" +
            "	      dud.doc_name AS `name`,	" +
            "	      IFNULL(dup.thumb, tdp.thumb) AS thumb,	" +
            "	      ts.width AS w,	" +
            "	      ts.height AS h,	" +
            "	      dud.create_time AS `timeOrder`,	" +
            "         '0' AS state, "+
            "	      uud.watermark,	" +
            "	      uud.wm_paid AS watermarkPay,	" +
            "	      uud.show_type_id AS st_id,	" +
            "	      0 AS duration	" +
            "	    FROM	" +
            "	      itbour.tg_type tt,	" +
            "	      itbour.tg_size ts,	" +
            "	      yjx.yjx_uni_user_doc uud,	" +
            "	      yjx.yjx_design_user_doc dud	" +
            "	      LEFT JOIN yjx.yjx_design_user_page dup	" +
            "	        ON dud.`doc_id` = dup.`doc_id`	" +
            "	        AND dup.del_flg = 0	" +
            "	        AND dup.page_index = 0	" +
            "	      LEFT JOIN itbour.tg_design_page tdp	" +
            "	        ON tdp.`design_id` = dud.`design_id`	" +
            "	        AND tdp.del_flg = 0	" +
            "	        AND tdp.page_index = 0	" +
            "	    WHERE uud.user_id = "+userId+"	" +
            "	      AND dud.uni_doc_id = uud.uni_doc_id	" +
            "	      AND dud.size_id = ts.size_id	" +
            "	      AND dud.type_id = tt.type_id	" +
            "	      AND tt.top_parent <> 1030	" +
            "	      AND dud.del_flg = 0	" +
            "	      AND uud.`del_flg` = 0	" +
            "	      AND tt.del_flg = 0	" +
            "	      AND ts.del_flg = 0   " +
            "	  UNION	" +
            "	  ALL	" +
            "	  SELECT	" +
            "	    uud.uni_doc_id AS id,	" +
            "	    '4' AS opType,	" +
            "	    cvud.doc_name AS `name`,	" +
            "	    CASE WHEN cvud.state = 3 THEN	" +
            "	    itbour.func_parse_string_array (cvud.thumbs, 0)	" +
            "	    ELSE	" +
            "	    'https://itbour-back.oss-cn-hangzhou.aliyuncs.com/image/U2/2018/07/05/171758351_GXBi4Eixs4iIdbVGsAYc'	" +
            "	    END	" +
            "	     AS thumb,	" +
            "	    ts.width AS w,	" +
            "	    ts.height AS h,	" +
            "	    cvud.create_time AS `timeOrder`,	" +
            "	    cvud.state,	" +
            "	    uud.watermark,	" +
            "	    uud.wm_paid AS watermarkPay,	" +
            "	    uud.show_type_id AS st_id,	" +
            "       cvud.duration "+
            "	  FROM	" +
            "	    yjx.yjx_uni_user_doc uud,	" +
            "	    yjx.yjx_cust_video_user_doc cvud	" +
            "	    LEFT JOIN itbour.tg_size ts	" +
            "	      ON cvud.size_id = ts.size_id	" +
            "	      AND ts.del_flg = 0	" +
            "	  WHERE uud.user_id = "+userId+"	" +
            "	    AND uud.uni_doc_id = cvud.uni_doc_id	" +
            "	    AND uud.user_id = cvud.user_id	" +
            "	    AND cvud.purpose = 0	" +
            "	    AND cvud.del_flg = 0	" +
            "	    AND uud.del_flg = 0) res	" +
            "	    LEFT JOIN yjx.yjx_show_type yst	" +
            "	      ON yst.del_flg = 0	" +
            "	      AND res.st_id = yst.st_id) res2	" +
            "	WHERE res2.opType IN ("+ opType +")	" +
            "	ORDER BY res2.timeOrder DESC,	" +
            "	  res2.id DESC	" ;
    if(!bForCnt){
        if (typeof pageNum !== 'undefined' && pageNum !== null &&
            typeof pageSize !== 'undefined' && pageSize !== null)
        {
            var skipCnt = Number(pageNum||'0')*Number(pageSize||'0');
            sql += " LIMIT " + skipCnt + "," + Number(pageSize||'0');
        }
    }
    return sql;
}

/**
 * 查询作品列表WEB
 *
 * @author 毛凯
 * @date 2017-1-26
 *
 * @param userId 用户唯一标识
 * @param pageNum 页码, 从0开始
 * @param pageSize 页容量, 大于0
 * @param opTypes 1 设计 2 视频
 * @param callback  回调
 *          err: 查询失败
 *          data : []
 *
 */
YjxUniUserDocModel.prototype.getUserWorkList_v1_0Web = function(userId,pageNum,pageSize,opTypes,callback){
    pool.pool.getConnection(function(getConnectErr,connection) {
        if (getConnectErr) {
            console.log('YjxUniUserDocModel.getUserWorkList_v1_0 getConnectErr: ' + getConnectErr);
            return callback(getConnectErr, null);
        }
        var sql = sql_getUserWorkListWeb(false,userId,pageNum,pageSize,opTypes);
        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            if (error) {
                console.log('YjxUserModel.getUserWorkList_v1_0 Err: ' + error);
                return callback(error, null);
            }
            return callback(null, results)
        })
    })
};


/**
 * 获取作品列表总个数WEB
 *
 * @author 毛凯
 * @date 2017-1-26
 *
 * @param userId 用户唯一标识
 * @param callback  回调
 *          err: 查询失败
 *          data : 数量
 */
YjxUniUserDocModel.prototype.getUserWorkListCntWeb = function(userId,pageNum,pageSize,opTypes,callback){
    pool.pool.getConnection(function(getConnectErr,connection) {
        if (getConnectErr) {
            console.log('YjxUniUserDocModel.getUserWorkList_v1_0 getConnectErr: ' + getConnectErr);
            return callback(getConnectErr, null);
        }
        var sql = sql_getUserWorkListWeb(true,userId,pageNum,pageSize,opTypes);
        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            if (error) {
                console.log('YjxUserModel.getUserWorkList_v1_0 Err: ' + error);
                return callback(error, null);
            }
            if (results && results[0]) {
                return callback(null, results[0].count);
            }
        })
    })
};



/**
 * 统计用户各个类型的作品个数
 *
 * @author 毛凯
 * @date 2018-7-4
 *
 * @param userId 用户唯一标识
 * @param callback  回调
 *          err: 查询失败
 *          data : 数量
 */
YjxUniUserDocModel.prototype.getUserDocTotalCount = function(userId,callback){
    pool.pool.getConnection(function(getConnectErr,connection) {
        if (getConnectErr) {
            console.log('YjxUniUserDocModel.getUserWorkList_v1_0 getConnectErr: ' + getConnectErr);
            return callback(getConnectErr, null);
        }
        var sql ="	SELECT	" +
                "	  *	" +
                "	FROM	" +
                "	  (SELECT	" +
                "	    COUNT(0) AS video	" +
                "	  FROM	" +
                "	    yjx.yjx_uni_user_doc uud,	" +
                "	    yjx.yjx_video_user_doc vud,	" +
                "	    itbour.video_design_doc vdd	" +
                "	    LEFT JOIN itbour.tg_size ts	" +
                "	      ON vdd.size_id = ts.size_id	" +
                "	      AND ts.del_flg = 0	" +
                "	  WHERE uud.user_id = "+userId+"	" +
                "	    AND uud.uni_doc_id = vud.uni_doc_id	" +
                "	    AND vud.design_id = vdd.design_id	" +
                "	    AND uud.del_flg = 0	" +
                "	    AND vud.del_flg = 0	" +
                "	    AND vdd.del_flg = 0) v,	" +
                "	  (SELECT	" +
                "	    COUNT(0) AS design	" +
                "	  FROM	" +
                "	    itbour.tg_type tt,	" +
                "	    itbour.tg_size ts,	" +
                "	    yjx.yjx_uni_user_doc uud,	" +
                "	    yjx.yjx_design_user_doc dud	" +
                "	    LEFT JOIN yjx.yjx_design_user_page dup	" +
                "	      ON dud.`doc_id` = dup.`doc_id`	" +
                "	      AND dup.del_flg = 0	" +
                "	      AND dup.page_index = 0	" +
                "	    LEFT JOIN itbour.tg_design_page tdp	" +
                "	      ON tdp.`design_id` = dud.`design_id`	" +
                "	      AND tdp.del_flg = 0	" +
                "	      AND tdp.page_index = 0	" +
                "	  WHERE uud.user_id = "+userId+"	" +
                "	    AND dud.uni_doc_id = uud.uni_doc_id	" +
                "	    AND dud.size_id = ts.size_id	" +
                "	    AND dud.type_id = tt.type_id	" +
                "	    AND tt.top_parent <> 1030	" +
                "	    AND dud.del_flg = 0	" +
                "	    AND uud.`del_flg` = 0	" +
                "	    AND tt.del_flg = 0	" +
                "	    AND ts.del_flg = 0) d,	" +
                "	  (SELECT	" +
                "	    COUNT(0) AS customVideo	" +
                "	  FROM	" +
                "	    yjx.yjx_uni_user_doc uud,	" +
                "	    yjx.yjx_cust_video_user_doc cvud	" +
                "	    LEFT JOIN itbour.tg_size ts	" +
                "	      ON cvud.size_id = ts.size_id	" +
                "	      AND ts.del_flg = 0	" +
                "	  WHERE uud.user_id = "+userId+"	" +
                "	    AND uud.uni_doc_id = cvud.uni_doc_id	" +
                "	    AND uud.user_id = cvud.user_id	" +
                "	    AND cvud.del_flg = 0	" +
                "	    AND uud.del_flg = 0) c	" ;
            console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            if (error) {
                console.log('YjxUserModel.getUserWorkList_v1_0 Err: ' + error);
                return callback(error, null);
            }
            if (results && results[0]) {
                return callback(null, results);
            }
        })
    })
};



/**
 * 检查统一用户作品类型
 *
 * @author 毛凯
 * @date 2017-8-7
 *
 * @param uniDocId 统一用户作品 id
 * @param callback
 *          err 检查出错,
 *          docInfo: {
 *              uni_doc_id : 用户统一作品 id,
 *              doc_type: 作品类型, 与模板一致, 10 标记设计作品, 20 标记 ppt 作品, 30 标记视频作品
 *              doc_id: 作品子表唯一标识
 *              doc_design_id: 作品对应的模板 id(子表)
 *              video_bg_id: 如果是视频分类且有视频背景, 则为背景 id, 没有视频背景则为 null
 *              video_bg_type: 如果是视频分类且有视频背景, 则为背景分类, 与 doc_type 分类一致: 10 标记设计作品, 20 标记 ppt 作品, 30 标记视频作品 没有视频背景则为 null
 *              bg_design_id: 背景对应的背景模板 id(子表)
 *              form_flg: '0:不使用表单, 1:使用表单(开启), 2:使用表单(关闭)',
 *              watermark: 作品是否有水印
 *              wm_paid: 是否已支付去水印费用
 *              modify_cnt:用户作品修改次数
 *              user_id: 作品对应用户标识
 *          }
 */
YjxUniUserDocModel.prototype.checkUniUserDocTypeByUniId = function (uniDocId, callback) {
    pool.pool.getConnection(function (getConErr, connection) {
        if(getConErr) {
            console.log('YjxUniUserDocMode [checkUniUserDocTypeByUniId] getConErr: ' + getConErr);
            return callback(getConErr, null);
        }
        var sql =
            ' SELECT yvud.uni_doc_id, ' +
            '        30 AS doc_type, ' +
            '        yvud.doc_id, ' +
            '        0 as video_bg, ' +
            '        yuud.watermark, ' +
            '        yuud.wm_paid, ' +
            '        yuud.form_flg, ' +
            '        yvud.design_id  AS design_id,' +
            '        DATE_FORMAT(yuud.create_time,"%Y-%m-%d") AS createDay, ' +
            '        yvud.modify_cnt AS modify_cnt,   '+
            '        yuud.user_id, ' +
            '        0 AS purpose ' +
            ' FROM yjx.yjx_video_user_doc yvud, ' +
            '      yjx.yjx_uni_user_doc yuud ' +
            ' WHERE yvud.uni_doc_id = ' + uniDocId +
            '   AND yuud.uni_doc_id = yvud.uni_doc_id ' +
            '   AND yvud.del_flg=0 ' +
            '   AND yuud.del_flg=0 '+
            // 增加自由创建的check
            'UNION ALL  	' +
            ' SELECT yuud.uni_doc_id,  	' +
            '		40 AS doc_type,  	' +
            '		cvud.doc_id,  	' +
            '		0 AS video_bg,  ' +
            '		yuud.watermark,  '	+
            '		yuud.wm_paid,  	' +
            '		yuud.form_flg,  ' +
            '		cvud.design_id  AS design_id, 	' +
            '		DATE_FORMAT(yuud.create_time,"%Y-%m-%d") AS createDay, ' +
            '		cvud.modify_cnt AS modify_cnt,   	' +
            '		cvud.user_id , 	' +
            '		cvud.purpose  	' +
            ' FROM   yjx.yjx_uni_user_doc yuud,	' +
            ' yjx.yjx_cust_video_user_doc cvud	' +
            ' WHERE cvud.uni_doc_id =  '+ uniDocId +
            '   AND cvud.uni_doc_id = yuud.uni_doc_id  ' +
            '   AND cvud.del_flg=0  	' +
            '   AND yuud.del_flg=0 	' ;

        console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();

            if(queryErr) {
                console.log('YjxUniUserDocMode [checkUniUserDocTypeByUniId] queryErr: ' + queryErr);
                return callback(queryErr, null);
            }
            if(!results || results.length <= 0) {
                return callback('该作品不存在或已删除', null);
            }
            return callback(queryErr, results);
        });
    })
};



/**
 * 获取用户作品水印及其基本信息
 *
 * @author 毛凯
 * @date 2017-8-7
 *
 * @param uniDocId 统一用户作品 id
 * @param callback
 *          err 检查出错,
 *          docInfo: {
 *              uni_doc_id : 用户统一作品 id,
 *              doc_type: 作品类型, 与模板一致, 10 标记设计作品, 20 标记 ppt 作品, 30 标记视频作品
 *              doc_id: 作品子表唯一标识
 *              doc_design_id: 作品对应的模板 id(子表)
 *              video_bg_id: 如果是视频分类且有视频背景, 则为背景 id, 没有视频背景则为 null
 *              video_bg_type: 如果是视频分类且有视频背景, 则为背景分类, 与 doc_type 分类一致: 10 标记设计作品, 20 标记 ppt 作品, 30 标记视频作品 没有视频背景则为 null
 *              bg_design_id: 背景对应的背景模板 id(子表)
 *              form_flg: '0:不使用表单, 1:使用表单(开启), 2:使用表单(关闭)',
 *              watermark: 作品是否有水印
 *              wm_paid: 是否已支付去水印费用
 *              modify_cnt:用户作品修改次数
 *              user_id: 作品对应用户标识
 *          }
 */
YjxUniUserDocModel.prototype.getUserVideoInfo = function (uniDocId, callback) {
    pool.pool.getConnection(function (getConErr, connection) {
        if(getConErr) {
            console.log('YjxUniUserDocMode [getUserVideoInfo] getConErr: ' + getConErr);
            return callback(getConErr, null);
        }
        var sql ="	SELECT	" +
                "	  yuud.*,	" +
                "	  ycvud.*	" +
                "	FROM	" +
                "	  yjx.yjx_uni_user_doc yuud,	" +
                "	  yjx.yjx_cust_video_user_doc ycvud	" +
                "	WHERE yuud.uni_doc_id = "+uniDocId+"	" +
                "	AND yuud.del_flg =0 	" +
                "	AND ycvud.del_flg = 0	" +
                "	AND yuud.uni_doc_id = ycvud.uni_doc_id	" ;
            console.log(sql);
        connection.query(sql, function (queryErr, results) {
            connection.release();

            if(queryErr) {
                console.log('YjxUniUserDocMode [getUserVideoInfo] queryErr: ' + queryErr);
                return callback(queryErr, null);
            }
            if(!results || results.length <= 0) {
                return callback('该作品不存在或已删除', null);
            }
            return callback(queryErr, results);
        });
    })
};


module.exports = YjxUniUserDocModel;